{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<style>.container { width:100% !important; }</style>"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Empty DataFrame\n",
      "Columns: []\n",
      "Index: []\n",
      "Empty DataFrame\n",
      "Columns: []\n",
      "Index: []\n",
      "                  long_openIntr  short_openIntr      净持仓   上一日净持仓  净持仓变化量\n",
      "date     variety                                                         \n",
      "20180410 A              87606.0        103538.0 -15932.0 -21032.0  5100.0\n",
      "         AG            249916.0        254001.0  -4085.0  -7516.0  3431.0\n",
      "         AL            257039.0        296998.0 -39959.0 -41884.0  1925.0\n",
      "         AP             36056.0         32587.0   3469.0   1248.0  2221.0\n",
      "         AU             94792.0         30458.0  64334.0  65115.0  -781.0\n"
     ]
    }
   ],
   "source": [
    "import pymongo\n",
    "import pandas as pd\n",
    "from pandas import Series,DataFrame\n",
    "import matplotlib as plt\n",
    "import matplotlib.dates as mdate\n",
    "from IPython.core.display import display, HTML\n",
    "display(HTML(\"<style>.container { width:100% !important; }</style>\"))\n",
    "#二行即可搞定画图中文乱码\n",
    "plt.rcParams['font.sans-serif']=['SimHei'] #用来正常显示中文标签\n",
    "plt.rcParams['axes.unicode_minus']=False #用来正常显示负号\n",
    "pd.set_option('display.width', None)  # 设置字符显示宽度\n",
    "pd.set_option('display.max_rows', None)  # 设置显示最大行\n",
    "pd.set_option('display.max_columns', None)  # 设置显示最大行\n",
    "\n",
    "#连接数据库\n",
    "client = pymongo.MongoClient('localhost',27017)\n",
    "futures = client.futures\n",
    "marketdata = futures.marketdata\n",
    "unit = futures.unit\n",
    "position = futures.position\n",
    "\n",
    "date='20180101'\n",
    "#加载数据\n",
    "marketdata = DataFrame(list(marketdata.find({'date': {'$gte':date}})))\n",
    "unit = DataFrame(list(unit.find()))\n",
    "position = DataFrame(list(position.find({'date': {'$gte': date}})))\n",
    "\n",
    "# #列名重名\n",
    "# position= position.rename(columns={'var':'variety'})\n",
    "\n",
    "#删除/选取某行含有特殊数值的列\n",
    "\n",
    "position=position.dropna()\n",
    "\n",
    "# ##选择需要显示的字段\n",
    "print(marketdata)\n",
    "position=position[['date','variety','symbol','long_openIntr','short_openIntr']]\n",
    "\n",
    "#修改日期格式，非常方便\n",
    "# marketdata['date'] = pd.to_datetime(marketdata['date'], format='%Y/%m/%d')\n",
    "# position['date'] = pd.to_datetime(position['date'], format='%Y/%m/%d')\n",
    "# 值替换replace()\n",
    "# position=position.replace(['PTA'],'TA')\n",
    "#汇总合约\n",
    "netPosition=position.groupby(['date','variety'])[['long_openIntr','short_openIntr']].sum()\n",
    "\n",
    "netPosition['净持仓']=netPosition.apply(lambda x:x['long_openIntr']-x['short_openIntr'],axis=1)\n",
    "netPosition['上一日净持仓']=netPosition.groupby('variety')['净持仓'].shift(1)\n",
    "netPosition['净持仓变化量']=netPosition.apply(lambda x: x['净持仓']-x['上一日净持仓'],axis=1)\n",
    "netPosition=netPosition.dropna()\n",
    "\n",
    "# marketdata.set_index(['date','variety'])\n",
    "#计算价格变化量\n",
    "\n",
    "\n",
    "print(marketdata.head())\n",
    "print(netPosition.head())\n",
    "# 并集\n",
    "# trade=pd.merge(netPosition,marketdata, on=['date','variety'],how='outer')\n",
    "# trade = trade.dropna(axis=0, how='any')\n",
    "# trade['交易信号'] = trade.apply(lambda x: 0 if x['净持仓变化量']*x['change']>=0  else 1 if x['净持仓变化量']>0 else -1,axis=1)\n",
    "# trade=trade.set_index('date')\n",
    "# trade = trade[trade['交易信号'] != 0]\n",
    "# del trade['上一日净持仓']\n",
    "# print(trade.head())\n",
    "\n",
    "\n",
    "# trade.to_csv(r\"c:\\futures1.csv\",mode='a',encoding='ANSI',header=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
